package com.example.mybatis_demo.mapper;

import com.example.mybatis_demo.model.UserInfo;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface UserInfoMapper {

    //增
    /*
     * 如何获取自增ID
     * 添加注解 @Options(useGeneratedKeys = true, keyProperty = "id")
     * void insertUserInfo() {
        UserInfo userInfo = new UserInfo();
        userInfo.setUserName("zyp");
        userInfo.setPassword("123456");
        userInfo.setAge(25);
        userInfo.setGender(1);
        userInfo.setPhone("18192112783");
        Integer result = userInfoMapper.insertUserInfo(userInfo);
        System.out.println(result + "" + userInfo.getId()); -> 获取userInfo.getId()
      }
     */
    @Insert("insert into userinfo (username, password, age,gender,phone) values (#{userName},#{password},#{age},#{gender},#{phone})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    Integer insertUserInfo(UserInfo userInfo);


    //参数为对象时，参数重命名用法
    @Insert("insert into userinfo (username, password, age,gender,phone) values (#{u.userName},#{u.password},#{u.age},#{u.gender},#{u.phone})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    Integer insertUserInfo2(@Param("u") UserInfo userInfo);


    //删
    @Delete("delete from userinfo where id = #{id}")
    Integer deleteUserInfoById(Integer id);


    //改
    @Update("update userinfo set username = #{userName} where id = #{id}")
    Integer updateUserInfoById(UserInfo userInfo);



    //查
    @Select("select * from userinfo") //正式开发环境下，尽量避免使用 * 需要查询哪些字段，就写哪些字段
    @Results(id = "BaseMap",value = {
            @Result(column = "delete_flag", property = "deleteFlag"),
            @Result(column = "create_time", property = "createTime"),
            @Result(column = "update_time", property = "updateTime")
    })
    List<UserInfo> selectAll();




    /*
     * 问题：数据库查询出来的字段和实体类字段不匹配如何解决，例如实体类是deleteFlag，数据库是delete_flag？
     * 解决方案1：使用 deleteFlag as deleteFlag赋值
     *
     * 解决方案2：采用@Result   column->数据库字段名，property->实体类名
     *  @Results(id = "BaseMap",value = {
     *   @Result(column = "delete_flag", property = "deleteFlag"),
     *   @Result(column = "create_time", property = "createTime"),
     *   @Result(column = "update_time", property = "updateTime")
     * })
     * 加上id = "BaseMap",方便后续复用代码
     *
     * 解决方案3：（基于解决方案2的id属性）
     * @ResultMap(value = "BaseMap")
     *
     * 解决方案4：使用配置的方式，自动将蛇形转驼峰，例如数据库字段是delete_flag，实体类字段是deleteFlag，则可以配置全局的映射规则，mybatis会自动将数据库字段转为实体类字段
     * 参考配置文件中的：map-underscore-to-camel-case: true #配置驼峰自动转换
     */


    @Select("select * from userinfo where username = #{username}")
    UserInfo getUserByUsername(String username);


    //参数重命名  将username重名命为UN，传递给占位符
    @Select("select * from userinfo where username = #{UN}")
    UserInfo getUserByUsername2(@Param("UN") String username);


    @Select("select * from userinfo where username = #{username} and delete_flag = ${deleteFlag}")
    UserInfo getUserByName_DeleteFlag(@Param("username") String username, @Param("deleteFlag") int deleteFlag);



    /*
     * #{}和${}的区别：
     * 1、字符串传参适用#{}
     * 2、${}是基于替换的，会存在SQL注入问题
     *    #{}会根据参数的类型决定要不要加上‘’
     * 3、order by 时只能使用${}进行执行
     * 4、模糊查询的时候使用${}  select * from userinfo where username like '%${username}%'
     * 假设username传递的是di，则最终执行结果为：select * from userinfo where username like '%di%';
     * 如果使用#{}，select * from userinfo where username like '%#{username}%'
     * 则最终执行的结果为：select * from userinfo where username like '%‘di'%'  ->  错误的SQL语句
     * 模糊查询使用#{}避免SQL注入问题->借助mysql内置函数concat
     * 5、SQL注入问题
     * 假设要执行的sql是：  select * from userinfo where username = 'admin'
     * #写法：select * from userinfo where username = #{username}
     * $写法：select * from userinfo where username = '${username}'
     * 假设传递的参数不是admin 是 ’or 1 = '1
     * #执行结果：select * from userinfo where username =
     * $执行结果：select * from userinfo where username = '' or 1 = '1'
     * SQL执行顺序：语法解析、SQL优化、SQL执行
     * 预编译SQL-> 性能更高，SQL执行的时候已经把语法解析和SQL优化部分做完了，不管传递的参数是什么，只能赋值给username这个字段 不会特殊给你处理 所以不存在SQL注入的问题
     * 即时SQL->性能较低，SQL执行的时候是要经过语法解析和SQL优化两步的 所以会存在SQL注入的问题
     * 6、表名、字段名等作为参数时，不可以使用# -》分库分表时常见
     */


    @Select("select * from userinfo where username like concat('%',#{userNameLike},'%')")
    UserInfo selectUserInfoByUserNameLike(String userNameLike);


    //可选参数插入
    @Insert("<script>" +
            "insert into userinfo (username,password," +
            "<if test='age != null'>age,</if>" +
            "<if test='gender != null'>gender,</if>" +
            "<if test='phone != null'>phone)</if>" +
            "values(#{userName},#{password}," +
            "<if test='age != null'>#{age},</if>" +
            "<if test='gender != null'>#{gender},</if>" +
            "<if test='phone != null'>#{phone})</if>" +
            "</script>")
    @ResultMap(value = "BaseMap")
    Integer insertUser3(UserInfo userInfo);
}
